<?php
namespace Emos\Db;

class Oci extends AbstractAdapter { 
    var $lowercaseFields = true;
    
    public function connect($params) {                
        $params = array(            
            'charset'   => 'utf8'            
        ) + $params;                      
        
        $this->connection = @oci_connect($params['user'], $params['password'], $params['connection_string'], $params['charset']);                                    
        if(!$this->connection) {
            $e = oci_error();
            throw new Exception("Can not connect to database '".$params['connection_string']."': ".@$e['error']);            
        }        
                
        $this->execQuery("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
    }
    
    function selectQuery($sql) {
        $data = array();
                
        $stid = @oci_parse($this->connection, $sql);
        if(!$stid) {        
            $e = oci_error($stid);
            throw new Exception("SQL query error {$sql} '{$e['message']}'", $sql);            
        }

       foreach($this->binds as $name=>$v) {
            $value = $v;
            $r = oci_bind_by_name($stid, ':'.$name, $value);            
            if($r === false) {
                throw new Exception("Unable to bind '{$name}'");
            }
            unset($value);
        }                                    
        
        $r = oci_execute($stid);
        if (!$r) {
            $e = oci_error($stid);            
            throw new Exception("SQL query error {$sql} '{$e['message']}'", $sql);            
        }        
        
        while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS)) {
            if($this->lowercaseFields) {
                $row2 = array();
                foreach($row as $k=>$v) {
                    $row2[ strtolower($k) ] = $v;
                }
                $data[] = $row2;        
            }
            else {
                $data[] = $row;        
            }
        }                
        
        oci_free_statement($stid);
        
        return $data;        
    }
    
    protected function addLimits($sql) {   
        $offset = (int)@$this->params['offset'];
        $limit  = (int)@$this->params['limit'];

        if(isset($this->params['offset'], $this->params['limit'])) {                        
            return "SELECT * FROM (SELECT a.*, ROWNUM AS rnum FROM ({$sql}) a WHERE ROWNUM <= ".($offset+$limit).") WHERE ".($offset+1)." <= RNUM";
        }                
        elseif(isset($this->params['limit'])) {
            return "SELECT * FROM ({$sql}) WHERE ROWNUM<=".($limit);                                                       
        }
        else {
            return $sql;
        }
    }    
    
    function execQuery($sql) {        
        $stid = oci_parse($this->connection, $sql);
        if (!$stid) {
            $e = oci_error($conn);            
            throw new Exception("SQL query error {$sql} '{$e['message']}'", $sql);            
        }        
		
        foreach($this->binds as $name=>$v) {
            $value = $v;
            
            $r = oci_bind_by_name($stid, ':'.$name, $value);                        
            if($r === false) {
                throw new Exception("Unable to bind '{$name}'");
            }
            
            unset($value);
        }                        
        
        $r = @oci_execute($stid);
        if (!$r) {
            $e = oci_error($stid);
            throw new Exception("SQL query error {$sql} '{$e['message']}'", $sql);            
        }  		

        return oci_num_rows($stid);
    }    
    
    function getInsertId() {
        return NULL;
    }

    public function escape($str) {
        return str_replace("'", "''", $str);
    }


    public function nameEscape($str) {
        return '"'.strtoupper($str).'"';
    }       
    
    
    protected function getParamExpression($name, $value) {
        $this->binds[$name] = $value;
        return ":{$name}";
    }
    
    
    public function updateDbSchema($struct) {
        $sql = array();
        foreach($struct['tables'] as $table) {
            $sql[] = "
                CREATE TABLE ".$this->nameEscape($table['name'])." (
                    ".$this->nameEscape($table['pk'])." INT NOT NULL,
                    CONSTRAINT ".$this->nameEscape($table['name'].'__pk')." PRIMARY KEY (".$this->nameEscape($table['pk']).")
                )";                    
            
            $sql[] = "
                CREATE SEQUENCE ".$this->nameEscape($table['name'].'__seq')." 
                START WITH 1 
                INCREMENT BY 1                 
            ";

            $sql[] = "
                CREATE OR REPLACE TRIGGER ".$this->nameEscape($table['name'].'__trg')."
                BEFORE INSERT ON ".$this->nameEscape($table['name'])." FOR EACH ROW
                WHEN (NEW.".$this->nameEscape($table['pk'])." IS NULL)    
                BEGIN
                  SELECT ".$this->nameEscape($table['name'].'__seq').".NEXTVAL
                  INTO :NEW.".$this->nameEscape($table['pk'])."
                  FROM DUAL;
                END;                          
            ";                  
 
            foreach($table['fkes'] as $field) {                
                $sql[] = "ALTER TABLE ".$this->nameEscape($table['name'])." MODIFY ".$this->nameEscape($field['name'])." INT NULL";                
                $sql[] = "ALTER TABLE ".$this->nameEscape($table['name'])." ADD ".$this->nameEscape($field['name'])." INT NULL";                                                          
                $sql[] = "CREATE INDEX ".$this->nameEscape($table['name'].'__'.$field['name'].'__fk')." ON ".$this->nameEscape($table['name'])." (".$this->nameEscape($field['name']).")";                
                
            }             
            
            foreach($table['fields'] as $field) {
                //$null = $field['nullable'] ? "NULL" : "NOT NULL";
                $null = "NULL"; // Let avoid Oracle 'empty string as null' problem
                
                $type = $this->translateType($field['type']);
                
                $sql[] = "ALTER TABLE ".$this->nameEscape($table['name'])." MODIFY ".$this->nameEscape($field['name'])." {$type}";                
                $sql[] = "ALTER TABLE ".$this->nameEscape($table['name'])." MODIFY ".$this->nameEscape($field['name'])." {$null}";    
                $sql[] = "ALTER TABLE ".$this->nameEscape($table['name'])." ADD ".$this->nameEscape($field['name'])." {$type}";                                   
            }            
        }
        
        foreach($sql as $cmd) {
            $fault = false;
            try {
                $this->exec($cmd);
            }
            catch(\Exception $ex) {
                $fault = true;
            }
            
            if(!$fault) {
                print "{$cmd}\n";
            }            
        } 
        
//          foreach($sql as $cmd) {        
//              print "{$cmd}\n";
//          }
    }

    protected function translateType($type) {        
        if($type == 'BIGINT') { 
            return 'NUMBER(19,0)';            
        }        
        elseif($type == 'DATETIME') { 
            return 'DATE';             
        }        
        elseif($type == 'DOUBLE') { 
            return 'FLOAT (24)';             
        }        
        elseif($type == 'TEXT') {
            return 'CLOB';             
        }        
        elseif($type == 'MEDIUMTEXT') { 
            return 'CLOB';             
        }        
        elseif($type == 'TIME') { 
            return 'DATE';             
        }        
        elseif(preg_match('/^ VARCHAR\( (\d+) \) $/xi', $type, $m)) { 
            return "VARCHAR2({$m[1]})";             
        }        
        else {
            return $type;            
        }
    }    
}
